using System;
using System.Reflection;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using DeVetma.admin.classes;
using DeVetma.admin.models.database.metadata;
using System.Data;

namespace DeVetma.admin.models.database
{
	public sealed class Database
	{
		private static volatile Database _instance;
		private static object instanceSync = new Object();

		public String connectionString { get; set; }

		private Database() { }

        /// <summary>
        /// Instantiate a single instance of the Database class,
        /// use this method on first call to database. The connection
        /// string is required otherwise there will be no connection
        /// to the database rendering this class without any use.
        /// (Thread Safe)
        /// </summary>
        /// <param name="connectionString">Required</param>
        /// <returns>A Singleton Instance of Database</returns>
		public static Database instance(string connectionString)
		{
			if (_instance != null)
				return _instance;

			if (string.IsNullOrWhiteSpace(connectionString))
				return null;

			lock (instanceSync)
			{
				if (_instance == null)
				{
					_instance = new Database();
					_instance.connectionString = connectionString;
				}
			}

			return _instance;
		}

        /// <summary>
        /// Returns the singleton instance of Database class.
        /// Only use this after you have instantiated or initialized
        /// the database class with the connection string.
        /// </summary>
        /// <returns>A Singleton Instance of Database</returns>
		public static Database instance()
		{
			return _instance;
		}

        /// <summary>
        /// Adds an entity into the database. The fields of the entity
        /// are automatically discovered through class reflection. The
        /// entity object provided has to have the Entity and EntityField
        /// attributes.
        /// </summary>
        /// <param name="entity">Required - Class that has Entity and EntityField Attribute</param>
        /// <returns>true if succeeded</returns>
		public bool addEntity(object entity)
		{
			Type entityType = entity.GetType();
			Entity entityMetadata = getMetadata<Entity>(entityType.GetCustomAttributes(
				typeof(Entity), false));

			if (entityMetadata == null || string.IsNullOrWhiteSpace(entityMetadata.table))
				return false;

            EntityPropertyInfo[] epis = getEntityPropertyInfo(entityType).ToArray();

            if (epis == null || epis.Length < 1)
                return false;

            List<SqlParameter> parameters = new List<SqlParameter>();
            string cols = "", vals = "";
            string queryString = "INSERT INTO " + entityMetadata.table.wrapString("[","]");

            foreach (var epi in epis)
            {
                if (epi.property.GetValue(entity, null) == null)
                    continue;

                cols += epi.field.column + ",";
                vals += "@" + epi.field.column + ",";
                parameters.Add(new SqlParameter("@" + epi.field.column, 
                    epi.property.GetValue(entity, null)));
            }

            cols = "(" + cols.Substring(0, cols.Length - 1) + ")";
            vals = "(" + vals.Substring(0, vals.Length - 1) + ")";
            queryString += " " + cols + " VALUES " + vals;

            if (doManipulationQuery(queryString, parameters.ToArray()) == 1)
                return true;

			return false;
		}

        /// <summary>
        /// Updates the changes made to the provided entity into
        /// the database. Entity has to have the Entity and EntityField
        /// attributes.
        /// </summary>
        /// <param name="entity">Required - Class that has Entity and EntityField Attribute</param>
        /// <returns>true if succeeded</returns>
		public bool updateEntity(object entity)
		{
			Type entityType = entity.GetType();
			Entity entityMetadata = getMetadata<Entity>(entityType.GetCustomAttributes(
				typeof(Entity), false));

			if (entityMetadata == null || string.IsNullOrWhiteSpace(entityMetadata.table))
				return false;

            EntityPropertyInfo[] epis = getEntityPropertyInfo(entityType).ToArray();

            if (epis == null || epis.Length < 1)
                return false;

            List<SqlParameter> parameters = new List<SqlParameter>();
            string attr = "", pkey = "";
            string queryString = "UPDATE " + entityMetadata.table.wrapString("[","]");

            foreach (var epi in epis)
            {
                string inset = epi.field.column + " = " + "@" + epi.field.column;
                object val =  epi.property.GetValue(entity, null);
                parameters.Add(new SqlParameter("@" + epi.field.column, 
                   val == null ? DBNull.Value : val));
                
                if (epi.field.keyField)
                {
                    pkey += inset + " AND ";
                    continue;
                }

                attr += inset + ",";
            }

            attr = "SET " + attr.Substring(0, attr.Length - 1);
            pkey = "WHERE " + pkey.Substring(0, pkey.Length - 5);
            queryString += " " + attr + " " + pkey;

            if (doManipulationQuery(queryString, parameters.ToArray()) == 1)
                return true;

            return false;
		}

        /// <summary>
        /// Gets entity from database with a single primary key.
        /// </summary>
        /// <typeparam name="T">The type of Entity - refer to modal.entities namespace</typeparam>
        /// <param name="key">required - the primary key value</param>
        /// <returns>Entity with the specified primary key</returns>
        public T getEntity<T>(object key) where T : class,new()
        {
            Dictionary<string, object> keyList = new Dictionary<string, object>();
            EntityPropertyInfo[] actualKeyList = getEntityKeyList(typeof(T)).ToArray();

            if (actualKeyList == null || actualKeyList.Length < 1)
                return null;

            keyList[actualKeyList[0].field.column] = key;

            return getEntity<T>(keyList);
        }

        /// <summary>
        /// Gets entity from database with composite primary key.
        /// </summary>
        /// <typeparam name="T">The type of Entity - refer to modal.entities namespace</typeparam>
        /// <param name="fieldKey">required - the primary key name and value pairs</param>
        /// <returns>Entity with the specified composite primary key</returns>
        public T getEntity<T>(Dictionary<string, object> fieldKey) where T:class,new()
        {
            Type entityType = typeof(T);
            Entity entityMetadata = getMetadata<Entity>(entityType.GetCustomAttributes(
                typeof(Entity), false));

            if (entityMetadata == null || string.IsNullOrWhiteSpace(entityMetadata.table))
                return null;

            List<SqlParameter> parameters = new List<SqlParameter>();
            string queryWhere = "";
            string queryString = "SELECT * FROM " + entityMetadata.table.wrapString("[","]");

            foreach (var key in fieldKey.Keys)
            {
                queryWhere += key + " = @" + key + " AND ";
                parameters.Add(new SqlParameter("@" + key, fieldKey[key]));
            }

            queryWhere = "WHERE " + queryWhere.Substring(0, queryWhere.Length - 5);
            queryString += " " + queryWhere;

            DataTable entityTable = doSelectQuery(queryString, parameters.ToArray());

            if (entityTable == null || entityTable.Rows.Count < 1)
                return null;

            EntityPropertyInfo[] epis = getEntityPropertyInfo(entityType).ToArray();
            T entity = new T();

            foreach (var epi in epis)
                setPropertyValue(entity, epi.property, epi.field,
                        entityTable.Rows[0][epi.field.column]);

            return entity;
        }

        /// <summary>
        /// Get list of entities for the the specified entity type from database that fulfill 
        /// the supplied query criteria.
        /// </summary>
        /// <typeparam name="T">The type of Entity - refer to modal.entities namespace</typeparam>
        /// <param name="whereCriteria">Query criteria, in column = @paramId form 
        /// e.g. mID = @mID or mID = @memberId</param>
        /// <param name="whereParams">The parameters that are used in the whereCriteria - 
        /// it should follow @paramId</param>
        /// <returns>List of Entities for the specified entity type that meet the 
        /// whereCriteria</returns>
		public List<T> getEntities<T>(string whereCriteria, SqlParameter[] whereParams) 
			where T:class,new()
		{
			List<T> toReturn = new List<T>();

			Type entityType = typeof(T);
			Entity entityMetadata = getMetadata<Entity>(entityType.GetCustomAttributes(
				typeof(Entity), false));

			if (entityMetadata == null || string.IsNullOrWhiteSpace(entityMetadata.table))
				return null;

            string queryString = "SELECT * FROM " + entityMetadata.table.wrapString("[","]");

            if (whereCriteria != null && !string.IsNullOrWhiteSpace(whereCriteria) &&
                whereParams != null && whereParams.Length > 0)
                queryString += " WHERE " + whereCriteria;

            DataTable rs = doSelectQuery(queryString, whereParams);
			EntityPropertyInfo[] epis = getEntityPropertyInfo(entityType).ToArray();

            for (int i = 0; i < rs.Rows.Count; ++i)
            {
                T entity = new T();

                foreach (var epi in epis)
                    setPropertyValue(entity, epi.property, epi.field,
                        rs.Rows[i][epi.field.column]);
                
                toReturn.Add(entity);
            }

			return toReturn;
		}

        /// <summary>
        /// Retrieves all the entities for the specified Entity Type
        /// </summary>
        /// <typeparam name="T">The type of Entity - refer to modal.entities namespace</typeparam>
        /// <returns>All entities of the specified entity type</returns>
		public List<T> getEntities<T>() where T:class,new()
		{
            return getEntities<T>(null, null);
		}

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool hasEntity<T>(T entity) where T : class,new()
        {
            Type entityType = typeof(T);
            Entity entityMetadata = getMetadata<Entity>(entityType.GetCustomAttributes(
                typeof(Entity), false));

            if (entityMetadata == null || string.IsNullOrWhiteSpace(entityMetadata.table))
                return false;

            EntityPropertyInfo[] keys = getEntityKeyList(entityType).ToArray();

            if (keys.Length == 1)
                return hasEntity<T>(keys[0].property.GetValue(entity, null));
            else if (keys.Length < 1)
                return false;

            return hasEntity<T>(toCompositeKeySet<T>(keys, entity));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="key"></param>
        /// <returns></returns>
        public bool hasEntity<T>(object key)
        {
            Type entityType = typeof(T);
            Entity entityMetadata = getMetadata<Entity>(entityType.GetCustomAttributes(
                typeof(Entity), false));

            if (entityMetadata == null || string.IsNullOrWhiteSpace(entityMetadata.table))
                return false;

            Dictionary<string, object> keySet = new Dictionary<string, object>();
            EntityPropertyInfo keyInfo = getEntityKeyList(entityType).FirstOrDefault();

            keySet[keyInfo.field.column] = key;

            return hasEntity<T>(keySet);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="compositeKey"></param>
        /// <returns></returns>
        public bool hasEntity<T>(Dictionary<string, object> compositeKey)
        {
            Type entityType = typeof(T);
            Entity entityMetadata = getMetadata<Entity>(entityType.GetCustomAttributes(
                typeof(Entity), false));

            if (entityMetadata == null || string.IsNullOrWhiteSpace(entityMetadata.table))
                return false;

            List<SqlParameter> whereParams = new List<SqlParameter>();
            string queryWhere = "";
            string queryString = "SELECT COUNT(*) FROM " + 
                entityMetadata.table.wrapString("[", "]");

            foreach (var key in compositeKey.Keys)
            {
                queryWhere += key + " = @" + key + " AND ";
                whereParams.Add(new SqlParameter
                {
                    ParameterName = "@" + key,
                    Value = compositeKey[key]
                });
            }

            queryWhere = queryWhere.Substring(0, queryWhere.Length - 5);
            queryString += queryWhere;

            object cnt = doScalarQuery(queryString, whereParams.ToArray());

            return (cnt != null && cnt is int && ((int)cnt) > 0);
        }

        /// <summary>
        /// Deletes all the records for the specified entity type.
        /// </summary>
        /// <typeparam name="T">The type of Entity - refer to modal.entities namespace</typeparam>
        /// <returns>true if succeed</returns>
        public bool deleteAll<T>() where T:class,new()
        {
            Type entityType = typeof(T);
            Entity entityMetadata = getMetadata<Entity>(entityType.GetCustomAttributes(
                typeof(Entity), false));

            if (entityMetadata == null || string.IsNullOrWhiteSpace(entityMetadata.table))
                return false;

            if (doManipulationQuery("DELETE FROM " + entityMetadata.table.wrapString("[","]"),
                    null) > 0)
                return true;

            return false;
        }

        /// <summary>
        /// Delete the specific entity.
        /// </summary>
        /// <param name="entity">Entity to be deleted</param>
        /// <returns>true if succeed</returns>
		public bool deleteEntity(object entity)
		{
            Type entityType = entity.GetType();
            Entity entityMetadata = getMetadata<Entity>(entityType.GetCustomAttributes(
                typeof(Entity), false));

            if (entityMetadata == null || string.IsNullOrWhiteSpace(entityMetadata.table))
                return false;

            EntityPropertyInfo[] keyList = getEntityKeyList(entityType).ToArray();
            
            List<SqlParameter> parameters = new List<SqlParameter>();
            string queryString = "DELETE FROM " + entityMetadata.table.wrapString("[","]");
            string keys = "";

            foreach (var k in keyList)
            {
                keys += k.field.column + " = @" + k.field.column + " AND ";
                parameters.Add(new SqlParameter("@" + k.field.column,
                    k.property.GetValue(entity, null)));
            }

            keys = "WHERE " + keys.Substring(0, keys.Length - 5);
            queryString += " " + keys;

            if (doManipulationQuery(queryString, parameters.ToArray()) == 1)
                return true;

            return false;
		}

        /// <summary>
        /// Perform direct select query on the database.
        /// </summary>
        /// <param name="queryString">Required - Full Query String</param>
        /// <param name="queryParams">Required if query string has parameters</param>
        /// <returns>DataTable filled with selected columns and queried rows</returns>
        public DataTable doSelectQuery(string queryString, SqlParameter[] queryParams)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    DataTable toReturn = new DataTable();
                    SqlCommand selector = new SqlCommand(queryString, connection);
                    SqlDataAdapter da = new SqlDataAdapter(selector);

                    if (queryParams != null && queryParams.Length > 0)
                    {
                        ensureSqlParameters(queryParams);
                        selector.Parameters.AddRange(queryParams);
                    }

                    connection.Open();
                    da.Fill(toReturn);

                    return toReturn;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Exception: " + DateTime.Now);
                System.Diagnostics.Debug.WriteLine(@"Exception was thrown at 
					Database.doSelectQuery(queryString,queryParams)");
                System.Diagnostics.Debug.WriteLine("Exception Message: ");
                System.Diagnostics.Debug.WriteLine(ex.Message);
                System.Diagnostics.Debug.WriteLine("Stack Trace: ");
                System.Diagnostics.Debug.WriteLine(ex.StackTrace);

                throw ex;
            }
        }

        /// <summary>
        /// Performs manipulation query on the database.
        /// </summary>
        /// <param name="queryString">Required - Full Query String</param>
        /// <param name="queryParams">Required if parameter is used in Query String</param>
        /// <returns>Number of rows affected by the query</returns>
        public int doManipulationQuery(string queryString, SqlParameter[] queryParams)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand updater = new SqlCommand(queryString, connection);

                    if (queryParams != null && queryParams.Length > 0)
                    {
                        ensureSqlParameters(queryParams);
                        updater.Parameters.AddRange(queryParams);
                    }
                    
                    connection.Open();

                    return updater.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Exception: " + DateTime.Now);
                System.Diagnostics.Debug.WriteLine(@"Exception was thrown at 
					Database.doManipulationQuery(queryString,queryParams)");
                System.Diagnostics.Debug.WriteLine("Exception Message: ");
                System.Diagnostics.Debug.WriteLine(ex.Message);
                System.Diagnostics.Debug.WriteLine("Stack Trace: ");
                System.Diagnostics.Debug.WriteLine(ex.StackTrace);

                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="queryString"></param>
        /// <param name="queryParams"></param>
        /// <returns></returns>
        public object doScalarQuery(string queryString, SqlParameter[] queryParams)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand executor = new SqlCommand(queryString, connection);

                    if (queryParams != null && queryParams.Length > 0)
                    {
                        ensureSqlParameters(queryParams);
                        executor.Parameters.AddRange(queryParams);
                    }

                    connection.Open();

                    return executor.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Exception: " + DateTime.Now);
                System.Diagnostics.Debug.WriteLine(@"Exception was thrown at 
					Database.doScalarQuery(queryString,queryParams)");
                System.Diagnostics.Debug.WriteLine("Exception Message: ");
                System.Diagnostics.Debug.WriteLine(ex.Message);
                System.Diagnostics.Debug.WriteLine("Stack Trace: ");
                System.Diagnostics.Debug.WriteLine(ex.StackTrace);

                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="entityType"></param>
        /// <returns></returns>
		private static List<EntityPropertyInfo> getEntityPropertyInfo(Type entityType)
		{
			PropertyInfo[] properties = entityType.GetProperties();
			List<EntityPropertyInfo> toReturn = new List<EntityPropertyInfo>();

			foreach (var prop in properties)
			{
				EntityField eField = getMetadata<EntityField>(prop.GetCustomAttributes(
					typeof(EntityField),false));

				if (eField != null)
					toReturn.Add( new EntityPropertyInfo { property = prop, field = eField } );
			}

			return toReturn;
		}

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="keyList"></param>
        /// <param name="entity"></param>
        /// <returns></returns>
        private static Dictionary<string, object> toCompositeKeySet<T>(
            EntityPropertyInfo[] keyList, T entity) where T:class,new()
        {
            Dictionary<string, object> keySet = new Dictionary<string, object>();

            foreach (var key in keyList)
            {
                if (key.field.keyField)
                    keySet.Add(key.field.column, key.property.GetValue(entity, null));
            }

            return keySet;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="entityType"></param>
        /// <returns></returns>
        private static List<EntityPropertyInfo> getEntityKeyList(Type entityType)
        {
            PropertyInfo[] properties = entityType.GetProperties();
            List<EntityPropertyInfo> toReturn = new List<EntityPropertyInfo>();

            foreach (var prop in properties)
            {
                EntityField eField = getMetadata<EntityField>(prop.GetCustomAttributes(
                    typeof(EntityField), false));

                if (eField != null && eField.keyField)
                    toReturn.Add(new EntityPropertyInfo { property = prop, field = eField });
            }

            return toReturn;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="metadata"></param>
        /// <returns></returns>
		private static T getMetadata<T>(object[] metadata) where T:class
		{
			foreach(var md in metadata)
				if (md is T)
					return (md as T);

			return null;
		}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="property"></param>
        /// <param name="field"></param>
        /// <param name="val"></param>
        private static void setPropertyValue(object obj, PropertyInfo property, 
            EntityField field, object val)
        {
            if (val == null || val is DBNull)
                val = field.nullValue;

            property.SetValue(obj, val, null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameters"></param>
        private static void ensureSqlParameters(SqlParameter[] parameters)
        {
            foreach (var parameter in parameters)
                if (parameter.Value == null)
                    parameter.Value = DBNull.Value;
        }

	}

	public class EntityPropertyInfo
	{
		public PropertyInfo property { get; set; }
		public EntityField field { get; set; }
	}

}